﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Text;
using System.Web.Script.Services;
using System.Web.Services;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;


namespace openMap
{
    /// <summary>
    /// Summary description for MapService
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class MapService : System.Web.Services.WebService
    {
        public static DataBaseHelpMan DatabseHelpMan;

        [WebMethod(Description = "获取sql的json数据")]
        public string GetJsonData(string sql)
        {   
            //DataTable dataTable = dataBaseHelper.GetDataTable(sql);
            ArrayList arrayList = new ArrayList();
            //foreach (DataRow dataRow in dataTable.Rows)
            //{
            //    Dictionary<string, object> dictionary = new Dictionary<string, object>(); //实例化一个参数集合
            //    foreach (DataColumn dataColumn in dataTable.Columns)
            //    {
            //        dictionary.Add(dataColumn.ColumnName, dataRow[dataColumn.ColumnName].ToString());
            //    }
            //    arrayList.Add(dictionary); //ArrayList集合中添加键值
            //}
            return JsonConvert.SerializeObject(arrayList);
        }

        [WebMethod(Description = "初始化mysql的连接")]
        public string InitMySql()
        {
            try
            {
                DatabseHelpMan = new MySqlHelpMan();
                DatabseHelpMan.Connecting = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
            }
            catch (Exception exception)
            {
                Console.WriteLine(exception.Message);
            }

            return DatabseHelpMan.Connecting;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string SetCellGeoJsonData()
        {
            const string sql = "Select longitude as x,latitude as y,ArcPts as points,LAC_CI as id,BTS_NAME as name,BCCH as value from dtdb_ningbo.cellinfo";

            StringBuilder stringBuilder = new StringBuilder();
            try
            {
                stringBuilder.AppendLine("{");
                stringBuilder.AppendLine("\"type\": \"FeatureCollection\",");
                stringBuilder.AppendLine(
                    "\"crs\": { \"type\": \"name\", \"properties\": { \"name\": \"urn:ogc:def:crs:OGC:1.3:CRS84\" } },");
                stringBuilder.AppendLine("\"features\": [");

                //处理datatable数据
                string geoFirst = "{ \"type\": \"Feature\", \"properties\": {";
                //" \"name\": \"";
                string getName = "name";
                string geoMiddle = "}, \"geometry\": { \"type\": \"";
                string geotype = "Point";
                string geocoord = "\", \"coordinates\": [";
                string geoPoints = string.Empty;
                string getLast = " ] } }";

                bool haveFeature = false;

                DataTable dataTable = DatabseHelpMan.GetDataTable(sql);
                if (dataTable != null && dataTable.Columns.Count > 1)
                {
                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        DataRow dataRow = dataTable.Rows[i];

                        getName = "\"name\":\"" + Convert.ToString(dataRow["name"]) + "\"" + ",\"id\":\"" + Convert.ToString(dataRow["id"]) + "\"," + "\"value\":\"" + Convert.ToString(dataRow["value"]) + "\"";

                        string x = Convert.ToString(dataRow["x"]);

                        string y = Convert.ToString(dataRow["y"]);

                        string xys = Convert.ToString(dataRow["points"]);

                        if (string.IsNullOrEmpty(x) || string.IsNullOrEmpty(y) ||string.IsNullOrEmpty(xys)) continue;

                        #region 处理point类型

                        //if (!string.IsNullOrEmpty(x) && !string.IsNullOrEmpty(y))
                        //{
                        //    geotype = "Point";
                        //    geoPoints = x + "," + y;
                        //    string pointLine = geoFirst + getName + geoMiddle + geotype + geocoord + geoPoints + getLast;

                        //    if (!haveFeature)
                        //    {
                        //        stringBuilder.AppendLine(pointLine);
                        //        haveFeature = true;
                        //    }
                        //    else
                        //        stringBuilder.AppendLine(","+pointLine);
                        //}

                        #endregion

                        #region 处理polygon类型

                        if (!string.IsNullOrEmpty(xys))
                        {
                            bool canAddpolygon = true;

                            geotype = "Polygon";

                            geoPoints = "[" + "[" + x + "," + y + "]";

                            foreach (string xy in xys.Split(';'))
                            {
                                string[] xy1 = xy.Split(',');
                                if (xy1.Length == 2)
                                {
                                    string x1 = xy1[0];
                                    string y1 = xy1[1];

                                    if (string.IsNullOrEmpty(x1) || string.IsNullOrEmpty(y1) || x1 == "0" || y1 == "0")
                                    {
                                        canAddpolygon = false;
                                        break;
                                    }

                                    geoPoints = geoPoints + "," + "[" + x1 + "," + y1 + "]";
                                }
                            }

                            if (canAddpolygon)
                            {
                                geoPoints = geoPoints + ",[" + x + "," + y + "]]";
                                string polygonLine = geoFirst + getName + geoMiddle + geotype + geocoord + geoPoints +
                                                     getLast;

                                if (!haveFeature)
                                {
                                    stringBuilder.AppendLine(polygonLine);
                                    haveFeature = true;
                                }
                                else
                                    stringBuilder.AppendLine("," + polygonLine);

                            }
                        }

                        #endregion
                    }
                }

                stringBuilder.AppendLine("]");
                stringBuilder.AppendLine("}");
              
            }
            catch (Exception exception)
            {
                //fileName = exception.Message;
                Console.WriteLine(exception.Message);
            }
            
            WriteGeoJsonFile(stringBuilder);

            string ss = Convert.ToString(stringBuilder);

            return "data/cell.geojson";
        }


        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string SetCellGeoJsonData1() 
        {
            //return "data/cell.geojson";

            const string sql = "Select longitude as x,latitude as y,ArcPts as points,LAC_CI as id,BTS_NAME as name,BCCH as value from dtdb_ningbo.cellinfo";

            StringBuilder stringBuilder = new StringBuilder();
            try
            {
                stringBuilder.Append("{");
                stringBuilder.Append("'type': 'FeatureCollection',");
                stringBuilder.Append(
                    "'crs': { 'type': 'name', 'properties': { 'name': 'urn:ogc:def:crs:OGC:1.3:CRS84'}},");
                stringBuilder.Append("'features': [");

                //处理datatable数据
                string geoFirst = "{'type':'Feature','properties': {";
                //" \"name\": \"";
                string getName = "name";
                string geoMiddle = "},'geometry': {'type':'";
                string geotype = "Point";
                string geocoord = "', 'coordinates': [";
                string geoPoints = string.Empty;
                string getLast = " ] } }";

                bool haveFeature = false;

                DataTable dataTable = DatabseHelpMan.GetDataTable(sql);
                if (dataTable != null && dataTable.Columns.Count > 1)
                {
                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        DataRow dataRow = dataTable.Rows[i];

                        getName = "'name':'" + Convert.ToString(dataRow["name"]) + "','id':'" + Convert.ToString(dataRow["id"]) + "','value':'" + Convert.ToString(dataRow["value"]) + "'";

                        string x = Convert.ToString(dataRow["x"]);

                        string y = Convert.ToString(dataRow["y"]);

                        string xys = Convert.ToString(dataRow["points"]);

                        if (string.IsNullOrEmpty(x) || string.IsNullOrEmpty(y) || string.IsNullOrEmpty(xys)) continue;

                        #region 处理point类型

                        //if (!string.IsNullOrEmpty(x) && !string.IsNullOrEmpty(y))
                        //{
                        //    geotype = "Point";
                        //    geoPoints = x + "," + y;
                        //    string pointLine = geoFirst + getName + geoMiddle + geotype + geocoord + geoPoints + getLast;

                        //    if (!haveFeature)
                        //    {
                        //        stringBuilder.AppendLine(pointLine);
                        //        haveFeature = true;
                        //    }
                        //    else
                        //        stringBuilder.AppendLine(","+pointLine);

                        //}

                        #endregion

                        #region 处理polygon类型

                        if (!string.IsNullOrEmpty(xys))
                        {
                            bool canAddpolygon = true;

                            geotype = "Polygon";

                            geoPoints = "[" + "[" + x + "," + y + "]";

                            foreach (string xy in xys.Split(';'))
                            {
                                string[] xy1 = xy.Split(',');
                                if (xy1.Length == 2)
                                {
                                    string x1 = xy1[0];
                                    string y1 = xy1[1];

                                    if (string.IsNullOrEmpty(x1) || string.IsNullOrEmpty(y1) || x1 == "0" || y1 == "0")
                                    {
                                        canAddpolygon = false;
                                        break;
                                    }

                                    geoPoints = geoPoints + "," + "[" + x1 + "," + y1 + "]";
                                }
                            }

                            if (canAddpolygon)
                            {
                                geoPoints = geoPoints + ",[" + x + "," + y + "]]";
                                string polygonLine = geoFirst + getName + geoMiddle + geotype + geocoord + geoPoints +
                                                     getLast;

                                if (!haveFeature)
                                {
                                    stringBuilder.Append(polygonLine);
                                    haveFeature = true;
                                }
                                else
                                    stringBuilder.Append("," + polygonLine);

                            }
                        }

                        #endregion
                    }
                }

                stringBuilder.Append("]}");
            }
            catch (Exception exception)
            {
                //fileName = exception.Message;
                Console.WriteLine(exception.Message);
            }

            WriteGeoJsonFile(stringBuilder);

            string ss = Convert.ToString(stringBuilder);
            
            string sss =
                "{'type': 'FeatureCollection','crs': { 'type': 'name', 'properties': { 'name': 'urn:ogc:def:crs:OGC:1.3:CRS84'}},'features': [{'type':'Feature','properties': {'name':'海口府城忠介路1','id':'30003_10001','value':'56'},'geometry': {'type':'Polygon', 'coordinates': [[[110.349,20.0019],[110.3497,20.0022],[110.3495,20.0025],[110.349,20.0019]] ] } },{'type':'Feature','properties': {'name':'海口府城忠介路2','id':'30003_10002','value':'64'},'geometry': {'type':'Polygon', 'coordinates': [[[110.349,20.0019],[110.3485,20.0014],[110.3488,20.0012],[110.349,20.0019]] ] } },{'type':'Feature','properties': {'name':'海口府城忠介路3','id':'30003_10003','value':'66'},'geometry': {'type':'Polygon', 'coordinates': [[[110.349,20.0019],[110.3484,20.0023],[110.3482,20.0020],[110.349,20.0019]] ] } },{'type':'Feature','properties': {'name':'海口白坡里169号1','id':'30022_10011','value':'58'},'geometry': {'type':'Polygon', 'coordinates': [[[110.332,20.0246],[110.3325,20.0251],[110.3322,20.0253],[110.332,20.0246]] ] } },{'type':'Feature','properties': {'name':'海口白坡里169号2','id':'30022_10012','value':'80'},'geometry': {'type':'Polygon', 'coordinates': [[[110.332,20.0246],[110.3314,20.0242],[110.3316,20.0240],[110.332,20.0246]] ] } },{'type':'Feature','properties': {'name':'海口白坡里169号3','id':'30022_10013','value':'94'},'geometry': {'type':'Polygon', 'coordinates': [[[110.332,20.0246],[110.3314,20.0250],[110.3312,20.0247],[110.332,20.0246]] ] } },{'type':'Feature','properties': {'name':'海口富苑1','id':'29953_10021','value':'84'},'geometry': {'type':'Polygon', 'coordinates': [[[110.347,20.0624],[110.3475,20.0635],[110.3470,20.0636],[110.347,20.0624]] ] } },{'type':'Feature','properties': {'name':'海口富苑2','id':'29953_10022','value':'90'},'geometry': {'type':'Polygon', 'coordinates': [[[110.347,20.0624],[110.3470,20.0612],[110.3475,20.0613],[110.347,20.0624]] ] } },{'type':'Feature','properties': {'name':'海口富苑3','id':'29953_10023','value':'76'},'geometry': {'type':'Polygon', 'coordinates': [[[110.347,20.0624],[110.3463,20.0614],[110.3468,20.0612],[110.347,20.0624]] ] } },{'type':'Feature','properties': {'name':'海口边防医院1','id':'30031_10031','value':'68'},'geometry': {'type':'Polygon', 'coordinates': [[[110.326,20.0533],[110.3268,20.0545],[110.3262,20.0547],[110.326,20.0533]] ] } },{'type':'Feature','properties': {'name':'海口边防医院2','id':'30031_10032','value':'72'},'geometry': {'type':'Polygon', 'coordinates': [[[110.326,20.0533],[110.3265,20.0520],[110.3270,20.0523],[110.326,20.0533]] ] } },{'type':'Feature','properties': {'name':'海口边防医院3','id':'30031_10033','value':'92'},'geometry': {'type':'Polygon', 'coordinates': [[[110.326,20.0533],[110.3255,20.0546],[110.3250,20.0543],[110.326,20.0533]] ] } },{'type':'Feature','properties': {'name':'海口府城云露里1','id':'30092_10041','value':'82'},'geometry': {'type':'Polygon', 'coordinates': [[[110.346,19.9994],[110.3468,19.9994],[110.3467,19.9997],[110.346,19.9994]] ] } },{'type':'Feature','properties': {'name':'海口府城云露里2','id':'30092_10042','value':'72'},'geometry': {'type':'Polygon', 'coordinates': [[[110.346,19.9994],[110.3454,19.9990],[110.3456,19.9988],[110.346,19.9994]] ] } },{'type':'Feature','properties': {'name':'海口府城云露里3','id':'30092_10043','value':'70'},'geometry': {'type':'Polygon', 'coordinates': [[[110.346,19.9994],[110.3454,19.9999],[110.3453,19.9996],[110.346,19.9994]] ] } }]}";
            WriteGeoJsonFile(new StringBuilder(sss));
            return "data/cell.geojson"; //;
        }

        private void WriteGeoJsonFile(StringBuilder stringBuilder)
        {
            string filepath = Server.MapPath("~") + "data\\";
            if (!Directory.Exists(filepath))
                Directory.CreateDirectory(filepath);

            string fullPath = Server.MapPath("~") + "data\\" + "cell.geojson";

            if (File.Exists(fullPath))
            {
                File.Delete(fullPath);
            }

            using (StreamWriter streamWriter = new StreamWriter(fullPath))
            {
                streamWriter.Write(stringBuilder);
                streamWriter.Flush();
                streamWriter.Close();
            }
        }

        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }
    }
}
